/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package net.wofly.sale.report.repository;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import net.wofly.sale.report.domain.CardExchangeDailyReport;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface CardExchangeDailyReportRepository
		extends PagingAndSortingRepository<CardExchangeDailyReport, String>, JpaSpecificationExecutor<CardExchangeDailyReport> {

	List<CardExchangeDailyReport> findByUserIDAndCountDate(String userID, Long countDate);

	@Query(value = "select new CardExchangeDailyReport(ce.salesmanUserID, c.customerName, et.exchangeTypeID, et.exchangeTypeName, " +
			"sum(cec.exchangeNum), sum(cec.subtotal)) from " +
			"CardExchange ce, CardExchangeContent cec, ExchangeType et, Customer c " +
			"where ce.modifyDate between :countStartDate and :countEndDate and ce.cardExchangeID = cec.cardExchangeID " +
			"and ce.salesmanUserID = c.userID and cec.exchangeTypeID = et.exchangeTypeID " +
			"group by ce.salesmanUserID, c.customerName, et.exchangeTypeID, et.exchangeTypeName ")
	List<CardExchangeDailyReport> findByCountDate(@Param(value = "countStartDate") Long countStartDate, @Param(value = "countEndDate") Long countEndDate);

	int deleteByCountDate(Long countDate);
}